package com.zretc.emptraining.daoimpl;

import java.util.List;

import com.zretc.emptraining.dao.TeacherEvaDao;
import com.zretc.emptraining.dbtools.DBTools;
import com.zretc.emptraining.dbtools.PageData;
import com.zretc.emptraining.entity.CourseEva;
import com.zretc.emptraining.entity.Teach;
import com.zretc.emptraining.entity.TeacherEva;

public class TeacherEvaDaoImpl implements TeacherEvaDao {

	@Override
	public boolean addTeacherEva(TeacherEva teacherEva) {
		// 调用工具类
		String sql = "insert into teachereva values (null,?,?,?,?)";
		int n = DBTools.getUpdate(sql, teacherEva.getEmpNo(), teacherEva.getCpNo(), teacherEva.getTeacherNo(),
				teacherEva.getEvalScore());
		return n > 0;
	}

	@Override
	public List<TeacherEva> getTeacherEvaAll() {
		String sql = "SELECT evalNo,tc.empNo,e.empName,tc.cpNo,c.courseName,tc.teacherNo,t.teacherName,evalScore\r\n" + 
				"FROM teachereva tc LEFT JOIN emp e ON tc.empNo = e.empNo\r\n" + 
				"LEFT JOIN courseplan cp ON cp.cpNo = tc.cpNo\r\n" + 
				"LEFT JOIN course c ON c.courseNo = cp.courseNo\r\n" + 
				"LEFT JOIN teach t ON t.teacherNo = tc.teacherNo";
		return (List<TeacherEva>) DBTools.exQuery(sql, TeacherEva.class, null);
	}
	
	// 模糊查询根据课程名
	@Override
	public PageData<TeacherEva> getTeacherEvaByCourseName(int page, int pageSize, String courseName) {
		String sql ="SELECT evalNo,tc.empNo,e.empName,tc.cpNo,c.courseName,tc.teacherNo,t.teacherName,evalScore\r\n" + 
				"FROM teachereva tc LEFT JOIN emp e ON tc.empNo = e.empNo\r\n" + 
				"LEFT JOIN courseplan cp ON cp.cpNo = tc.cpNo\r\n" + 
				"LEFT JOIN course c ON c.courseNo = cp.courseNo\r\n" + 
				"LEFT JOIN teach t ON t.teacherNo = tc.teacherNo WHERE courseName like ?";
		return DBTools.exQueryByPage(sql, TeacherEva.class, page, pageSize, "%"+courseName+"%");
	}
	// 模糊查询根据教员名
	@Override
	public PageData<TeacherEva> getTeacherEvaByTeacherName(int page, int pageSize, String teacherName) {
		String sql ="SELECT evalNo,tc.empNo,e.empName,tc.cpNo,c.courseName,tc.teacherNo,t.teacherName,evalScore\r\n" + 
				"FROM teachereva tc LEFT JOIN emp e ON tc.empNo = e.empNo\r\n" + 
				"LEFT JOIN courseplan cp ON cp.cpNo = tc.cpNo\r\n" + 
				"LEFT JOIN course c ON c.courseNo = cp.courseNo\r\n" + 
				"LEFT JOIN teach t ON t.teacherNo = tc.teacherNo WHERE teacherName like ?";
		return DBTools.exQueryByPage(sql, TeacherEva.class, page, pageSize, "%"+teacherName+"%");
	}
	
	@Override
	public List<TeacherEva> doTeacherEva(int empNo, int cpNo) {
		String sql = "select evalScore from teachereva where empNo=? and cpNo=?";
		return (List<TeacherEva>) DBTools.exQuery(sql, TeacherEva.class, empNo, cpNo);
	}
	
	@Override
	public PageData<TeacherEva> getTeacherEvaByPage(int page, int pageSize, String empNoQuery) {
		String sql = "select * from teachereva where empNo like ?";
		return DBTools.exQueryByPage(sql, TeacherEva.class, page, pageSize, "%" + empNoQuery + "%");
	}
	
	@Override
	public PageData<Teach> getTeacherEvaShow(int page, int pageSize) {
		String sql = "SELECT  teach.teacherNo,teach.teacherName,teach.teacherXl,teach.teacherPic,(select avg(teachereva.evalScore)  FROM teachereva where teach.teacherNo = teachereva.teacherNo ) as evalScore,teach.teacherSex,teach.teacherDesc\r\n" + 
				"FROM teach ";
		return DBTools.exQueryByPage(sql, Teach.class, page, pageSize);
	}
}
